-- 该文档针对老版本用户

--

ALTER TABLE `student` ADD COLUMN `credit` bigint(20) NULL COMMENT '学生积分';
ALTER TABLE `course_comment` ADD COLUMN `order_id` bigint(20) NULL COMMENT '订单id';
ALTER TABLE `subject` ADD COLUMN `sort_num` int(11) NULL COMMENT '排序值';

-- 增量变动20220429  更新预约记录表和信息表，需要直接替换表
DROP TABLE IF EXISTS `appointment`;
CREATE TABLE `appointment`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `lesson_id` bigint(20) NOT NULL COMMENT '试听课次',
  `student_id` bigint(20) NOT NULL COMMENT '学员id',
  `add_time` datetime(0) NULL DEFAULT NULL COMMENT '预约时间',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `verify_state` tinyint(2) NULL DEFAULT 1 COMMENT '审核状态',
  `verify_time` datetime(0) NULL DEFAULT NULL COMMENT '审核时间',
  `verify_staff` bigint(20) NULL DEFAULT NULL COMMENT '审核人',
  `verify_remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '审核内容',
  `course_id` bigint(20) NULL DEFAULT NULL COMMENT '所属课程',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `student_id`(`student_id`) USING BTREE,
  INDEX `lesson_id`(`lesson_id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 11 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '试听预约记录' ROW_FORMAT = Dynamic;


DROP TABLE IF EXISTS `message`;
CREATE TABLE `message`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '发送标题',
  `content` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '发送内容',
  `to_id` bigint(20) NOT NULL COMMENT '接收人ID 0所有',
  `from_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '发送人ID',
  `to_type` tinyint(4) NULL DEFAULT NULL COMMENT '接受者类型  1 老师 2 学生',
  `from_type` tinyint(4) NULL DEFAULT NULL COMMENT '发送者类型 0 系统 1 老师 2 学生',
  `send_time` datetime(0) NULL DEFAULT NULL COMMENT '发送时间',
  `add_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `creator` bigint(20) NULL DEFAULT NULL COMMENT '创建者',
  `deleted` tinyint(1) NULL DEFAULT NULL COMMENT '删除标记',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 9 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '站内信' ROW_FORMAT = Dynamic;



-- 增量变动 20220425
- 2022.4.25 实现预约功能
- 2022.3.28 实现系统设置管理定时任务
- 2022.3.17 实现微信配置的数据库存储

```
ALTER TABLE `org` DROP COLUMN `ship_routes`;
ALTER TABLE `org` ADD COLUMN `name_path` varchar(255) NULL COMMENT '机构名路径' AFTER `id_path`;
ALTER TABLE `course` ADD COLUMN `bookable` tinyint(1) NULL DEFAULT 0 COMMENT '是否可以预约';
ALTER TABLE `lesson` ADD COLUMN `bookable` tinyint(1) NULL DEFAULT 0 COMMENT '是否开放预约';
ALTER TABLE `appointment` ADD COLUMN `course_id` bigint(20) NOT NULL COMMENT '所属课程';
ALTER TABLE `staff_orginfo` ADD COLUMN `id_path` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT 'id全路径' ;
ALTER TABLE `student` ADD COLUMN `grade` year NULL COMMENT '年级';
ALTER TABLE `student` ADD COLUMN `join_date` date NULL DEFAULT NULL COMMENT '入学日期';
ALTER TABLE `lesson_student` ADD COLUMN `add_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',

INSERT INTO `setting`(`id`, `name`, `code`, `remark`, `sort_num`) VALUES (4, '微信公众号设置', 'wx_mp_setting', NULL, 0);
INSERT INTO `setting_option`(`id`, `setting_id`, `name`, `code`, `value`, `value_type`, `info`, `sort_num`) VALUES (10, 4, '公众号AppId', 'wx_mp_app_id', 'app_idAAAAA', 'str', NULL, 0);
INSERT INTO `setting_option`(`id`, `setting_id`, `name`, `code`, `value`, `value_type`, `info`, `sort_num`) VALUES (11, 4, '公众号Secret', 'wx_mp_secret', 'secret', 'str', NULL, 0);
INSERT INTO `setting_option`(`id`, `setting_id`, `name`, `code`, `value`, `value_type`, `info`, `sort_num`) VALUES (12, 4, '公众号Token', 'wx_mp_token', 'token', 'str', NULL, 0);
INSERT INTO `setting_option`(`id`, `setting_id`, `name`, `code`, `value`, `value_type`, `info`, `sort_num`) VALUES (13, 4, '公众号AesKey', 'wx_mp_aes_key', 'aes_key', 'str', NULL, 0);
INSERT INTO `setting_option`(`id`, `setting_id`, `name`, `code`, `value`, `value_type`, `info`, `sort_num`) VALUES (14, 1, '每天上课提醒时间', 'lesson_remind_time', '18:30', 'time', NULL, 0);
INSERT INTO `setting_option`(`id`, `setting_id`, `name`, `code`, `value`, `value_type`, `info`, `sort_num`) VALUES (15, 1, '每天上课次数预警时间', 'lesson_count_warning_time', '7:00', 'time', NULL, 0);

CREATE TABLE `quartz_job` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
  `job_name` varchar(255) DEFAULT NULL COMMENT '任务名称',
  `group_name` varchar(255) DEFAULT NULL COMMENT '任务分组',
  `job_class` varchar(255) DEFAULT NULL COMMENT '执行类路径',
  `status` tinyint(1) DEFAULT '1' COMMENT '状态',
  `cron_expression` varchar(255) DEFAULT NULL COMMENT 'cron表达式',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE=MyISAM AUTO_INCREMENT=12 DEFAULT CHARSET=utf8 COMMENT='定时任务';


DROP TABLE IF EXISTS `appointment`;
CREATE TABLE `appointment`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT,
  `lesson_id` bigint(20) NOT NULL COMMENT '试听课次',
  `student_id` bigint(20) NOT NULL COMMENT '学员id',
  `add_time` datetime(0) NULL DEFAULT NULL COMMENT '预约时间',
  `remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '备注',
  `verify_state` tinyint(2) NULL DEFAULT 1 COMMENT '审核状态',
  `verify_time` datetime(0) NULL DEFAULT NULL COMMENT '审核时间',
  `verify_staff` bigint(20) NULL DEFAULT NULL COMMENT '审核人',
  `verify_remark` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '审核内容',
  `course_id` bigint(20) NULL DEFAULT NULL COMMENT '所属课程',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `student_id`(`student_id`) USING BTREE,
  INDEX `lesson_id`(`lesson_id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '试听预约记录' ROW_FORMAT = Dynamic;


DROP TABLE IF EXISTS `message`;
CREATE TABLE `message`  (
  `id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `title` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NOT NULL COMMENT '发送标题',
  `content` varchar(500) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci NULL DEFAULT NULL COMMENT '发送内容',
  `to_id` bigint(20) NOT NULL COMMENT '接收人ID 0所有',
  `from_id` bigint(20) NOT NULL DEFAULT 0 COMMENT '发送人ID',
  `to_type` tinyint(4) NULL DEFAULT NULL COMMENT '接受者类型  1 老师 2 学生',
  `from_type` tinyint(4) NULL DEFAULT NULL COMMENT '发送者类型 0 系统 1 老师 2 学生',
  `send_time` datetime(0) NULL DEFAULT NULL COMMENT '发送时间',
  `add_time` datetime(0) NULL DEFAULT NULL COMMENT '创建时间',
  `creator` bigint(20) NULL DEFAULT NULL COMMENT '创建者',
  `deleted` tinyint(1) NULL DEFAULT NULL COMMENT '删除标记',
  PRIMARY KEY (`id`) USING BTREE
) ENGINE = MyISAM AUTO_INCREMENT = 1 CHARACTER SET = utf8mb4 COLLATE = utf8mb4_general_ci COMMENT = '站内信' ROW_FORMAT = Dynamic;


```